import web.data.DataUtil as dbutil
import web.entity.Article as articleEntity

def selectAllArticle():
    clist = list()
    rows = dbutil.selectSqllite("select id,title,`time`,`end` from pi_article order by sort,time")
    for r in rows:
        colnow = articleEntity.Article(r[0], r[1], '', '', r[2], r[3])
        clist.append(colnow)
    return clist

def selectAllArticleAndContent():
    clist = list()
    rows = dbutil.selectSqllite("select id,title,content,sort,`time`,`end` from pi_article order by sort,time")
    for r in rows:
        colnow = articleEntity.Article(r[0], r[1], r[2], r[3], r[4], r[5])
        clist.append(colnow)
    return clist

def selectArticleByCid(cid):
    clist = list()
    sql = "select id,title,`time`,`end` from pi_article where id in (select aid from pi_ca where cid = %s) order by sort,time" % cid
    rows = dbutil.selectSqllite(sql)
    for r in rows:
        colnow = articleEntity.Article(r[0], r[1], '', '', r[2], r[3])
        clist.append(colnow)
    return clist

def selectArticleFind(parameter):
    clist = list()
    sql = "select id,title,`time`,`end` from pi_article where id in (select aid from pi_ca where title like '%" + parameter + "%') order by sort,time"
    print(sql)
    rows = dbutil.selectSqllite(sql)
    for r in rows:
        colnow = articleEntity.Article(r[0], r[1], '', '', r[2], r[3])
        clist.append(colnow)
    return clist

def selectArticleById(id):
    sql = "select id,title,content,sort,`time`,`end` from pi_article where id = %s" % id
    r = dbutil.selectOneSqllite(sql)
    if r != 0:
        return articleEntity.Article(r[0], r[1], r[2], r[3], r[4], r[5])
    else:
        return articleEntity.Article(0, '', '', '', '', '')

def createArticle(title, content, sort, time, end):
    sql = "INSERT INTO pi_article(`title`,`content`,`sort`,`time`,`end`) VALUES('%s','%s','%s','%s','%s')" % (title, content, sort, time, end)
    return dbutil.createSqllite(sql)


def updateArticle(id, title, content, sort, time, end):
    sql = "update pi_article set `title` = '%s',`content` = '%s',`sort` = '%s',`time` = '%s', `end` = '%s' where id = '%s'" % (title, content, sort, time, end, id)
    dbutil.updateSqllite(sql)

def deleteArticleById(id):
    sql1 = "delete from pi_article where id = %s" % id
    sql2 = "delete from pi_ca where aid = %s" % id
    dbutil.deleteSqllite(sql1)
    dbutil.deleteSqllite(sql2)